using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace HermesPortal.Core.DAO
{
    internal class UserDAO
    {

        public static List<Creator> GetCreators()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlDataReader reader = null;
            List<Creator> creators = new List<Creator>();
            using (SqlCommand cmd = new SqlCommand("CreatorsGet", conn))
            {


                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    creators.Add(LoadCreator(reader));
                }
                reader.Close();
                conn.Close();
            }
            return creators;

        }
        public static List<Creator> GetCreatorsBySaga(int sagaId)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlDataReader reader = null;
            List<Creator> creators = new List<Creator>();
            using (SqlCommand cmd = new SqlCommand("CreatorsGetBySaga", conn))
            {


                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaId);
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    creators.Add(LoadCreator(reader));
                }
                reader.Close();
                conn.Close();
            }
            return creators;
        }
        public static Creator GetCreator(int creatorId)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlDataReader reader = null;
            Creator creator = null;
            using (SqlCommand cmd = new SqlCommand("CreatorGet", conn))
            {


                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", creatorId);
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    creator = LoadCreator(reader);
                }
                reader.Close();
                conn.Close();
            }
            return creator;

        }

        public static Creator GetCreatorByUserName(string userName)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlDataReader reader = null;
            Creator creator = null;
            using (SqlCommand cmd = new SqlCommand("CreatorGetByUserName", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    creator = LoadCreator(reader);
                }
                reader.Close();
                conn.Close();
            }
            return creator;
        }

        public static Creator GetCreatorBySaga(int creatorId, int sagaId)
        {

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            SqlDataReader reader = null;
            Creator creator = null;
            using (SqlCommand cmd = new SqlCommand("CreatorGetBySaga", conn))
            {
               
 
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", creatorId);
                cmd.Parameters.AddWithValue("@SagaID", sagaId); 
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    creator = LoadCreator(reader);
                }
                reader.Close();
                conn.Close();
            }
            return creator;
        }

      
        public static void SetCreatorRoleForSaga(int creatorId, int sagaId, int sagaroleId)
        {

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            using (SqlCommand cmd = new SqlCommand("CreatorSetRoleForSaga", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", creatorId);
                cmd.Parameters.AddWithValue("@SagaID", sagaId);
                cmd.Parameters.AddWithValue("@SagaRoleID", sagaroleId);  
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }

        }

        public static int GetCreatorRoleForSaga(int creatorId, int sagaId)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            int sagaRole = 0;
            using (SqlCommand cmd = new SqlCommand("CreatorGetRoleForSaga", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", creatorId);
                cmd.Parameters.AddWithValue("@SagaID", sagaId);
                cmd.Connection.Open();
                object objSagaRole = cmd.ExecuteScalar();
                conn.Close();
                if (objSagaRole != null)
                    sagaRole = Convert.ToInt32(objSagaRole); 
            }
            return sagaRole;
        }

        public static void UpdateCreator( Creator creator)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            using (SqlCommand cmd = new SqlCommand("CreatorUpdate", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CreatorID", creator.Id  );
                cmd.Parameters.AddWithValue("@DefaultSagaID", creator.DefaultSagaID  );
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }


        }
        internal static void AddCreator(string userName, string applicationName )
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            using (SqlCommand cmd = new SqlCommand("CreatorAdd", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Parameters.AddWithValue("@ApplicationName", applicationName);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
        #region private helper methods
        private static Creator LoadCreator(SqlDataReader reader)
        {
            Creator creator = null;
            if (reader != null && !reader.IsClosed)
            {
                creator = new Creator(); 
                creator.Id = (int)reader["CreatorID"];
                if (reader["DefaultSagaID"] != DBNull.Value) creator.DefaultSagaID = (int)reader["DefaultSagaID"];
                if (reader["IsBrowser"] != DBNull.Value  ) creator.IsBrowser = (bool)reader["IsBrowser"];
                if (reader["CanAdd"] != DBNull.Value) creator.CanAdd = (bool)reader["CanAdd"];
                if (reader["UserName"] != DBNull.Value) creator.UserName = (string)reader["UserName"];
                if (reader["RoleName"] != DBNull.Value) creator.SagaRoleName = (string)reader["RoleName"];
                if (reader["SagaRoleID"] != DBNull.Value) creator.SagaRoleID = (SagaRoles)reader["SagaRoleID"];  
            }

            return creator;
        }
        #endregion



       
    }


}
